package edu.upc.bolsatrabajo.model.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import edu.upc.bolsatrabajo.model.domain.Postulante;
import edu.upc.bolsatrabajo.model.iface.IPostulante;

@Repository
public class PostulanteImpl implements IPostulante {

	@Autowired
	private DataSource dataSource;

	Connection connection = null;
	PreparedStatement statement = null;
	ResultSet resultSet = null;

	public List<Postulante> listarPostulantes(Postulante oPostulante) {
		List<Postulante> postulantes = new ArrayList<Postulante>();

		String sqlListarPostulantes = "SELECT * FROM TAB_POSTULANTE;";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlListarPostulantes);
			resultSet = statement.executeQuery(sqlListarPostulantes);

			while (resultSet.next()) {
				oPostulante = new Postulante();
				oPostulante.setNombreUsuario(resultSet.getString("C_CODIGO_USUARIO"));
				oPostulante.setTituloAnuncio(resultSet.getString("C_TITULO_ANUNCIO"));
				oPostulante.setNombres(resultSet.getString("C_NOMBRES"));
				oPostulante.setApellidoPaterno(resultSet.getString("C_APELLIDO_PATERNO"));
				oPostulante.setApellidoMaterno(resultSet.getString("C_APELLIDO_MATERNO"));
				postulantes.add(oPostulante);
			}

		} catch (Exception e) {
			throw new RuntimeException(
					"SQL exception occurred finding Postulante", e);
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}

		return postulantes;
	}

	public Postulante obtenerPostulante(Postulante oPostulante) {
		// TODO Auto-generated method stub
		return null;
	}

	public int insertarPostulante(Postulante oPostulante) {

		int respuesta = 0;
		String sqlInsertarPostulantes = "INSERT INTO TAB_POSTULANTE " +
				"(C_CODIGO_USUARIO, C_NOMBRES, C_APELLIDO_PATERNO, C_APELLIDO_MATERNO) " +
				"VALUES (?,?,?,?)";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlInsertarPostulantes);
			statement.setString(1, oPostulante.getNombreUsuario());
			statement.setString(2, oPostulante.getNombres());
			statement.setString(3, oPostulante.getApellidoPaterno());
			statement.setString(4, oPostulante.getApellidoMaterno());
			statement.executeUpdate();

			respuesta = 1;

		} catch (Exception e) {
			throw new RuntimeException("SQL exception occurred insert Postulante", e); 
			//e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}
		return respuesta;
	}

	public int actualizarPostulante(Postulante oPostulante) {

		int respuesta = 0;
		String sqlActualizarPostulantes = "UPDATE TAB_POSTULANTE SET " +
				" C_NOMBRES = ? , C_APELLIDO_PATERNO = ? , C_APELLIDO_MATERNO = ? " +
				" WHERE N_CODIGO_POSTULANTE = ?";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlActualizarPostulantes);
			statement.setString(1, oPostulante.getNombres());
			statement.setString(2, oPostulante.getApellidoPaterno());
			statement.setString(3, oPostulante.getApellidoMaterno());
			statement.setInt(4, oPostulante.getCodigo());
			statement.executeUpdate();
			
			respuesta = 1;

		} catch (Exception e) {
			//throw new RuntimeException("SQL exception occurred update Postulante", e);
			e.printStackTrace();
			
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}
		return respuesta;
	}

	public int eliminarPostulante(Postulante oPostulante) {

		int respuesta = 0;
		String sqlEliminarPostulante = "DELETE FROM TAB_POSTULANTE WHERE " +
				" N_CODIGO_POSTULANTE = ? ";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlEliminarPostulante);
			statement.setInt(1, oPostulante.getCodigo());
			statement.executeQuery(sqlEliminarPostulante);
			
			respuesta = 1;

		} catch (Exception e) {
			throw new RuntimeException("SQL exception occurred finding Usuario", e); 
			
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}
		return respuesta;
	}

}
